﻿using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Windows;
using Microsoft.Win32;

namespace ExportExcel
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            var openFileDialog = new OpenFileDialog { Filter = "Excel documents (.xls,*.xlsx)|*.xls?" };
            if (openFileDialog.ShowDialog() == true)
            {
                this.textBox1.Text = openFileDialog.FileName;
            }
        }
        public static DataTable GetExcelToDataTableBySheet(string filePath, string sheetName)
        {
            var strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();
                var ds = new DataSet();
                var odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), conn);
                odda.Fill(ds, sheetName);
                conn.Close();
                return ds.Tables[0];
            }
        }
        private void button2_Click(object sender, RoutedEventArgs e)
        {
            var filePath = this.textBox1.Text;
            if (!string.IsNullOrWhiteSpace(filePath) && System.IO.File.Exists(filePath))
            {
                var dirPath = System.IO.Path.GetDirectoryName(filePath);
                var fileName = System.IO.Path.GetFileNameWithoutExtension(filePath);
                var outputFilePath = System.IO.Path.Combine(dirPath, fileName + ".txt");
                var table = GetExcelToDataTableBySheet(filePath, "Sheet1$");
                var skipRows = this.checkBox1.IsChecked.HasValue && (bool)this.checkBox1.IsChecked ? 0 : 1;
                using (var sw = new StreamWriter(outputFilePath))
                {
                    foreach (var line in (from DataRow dr in table.Rows select string.Join(",", dr.ItemArray.Select(s => s.ToString()))).Skip(skipRows))
                    {
                        sw.WriteLine(line);
                    }
                    sw.Flush();
                    sw.Close();
                }
                this.label2.Content = "生成成功:" + outputFilePath;
            }
            else
            {
                MessageBox.Show("请选择文件");
            }
        }
    }
}
